﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using BusinessEntity;

namespace BusinessService
{
    /// <summary>
    /// 角色
    /// </summary>
    public class UserRoleService
    {
        #region 添加角色
        /// <summary>
        /// 添加角色
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddNew(UserRole model)
        {
            object obj = SqlHelper.ExecuteScalar(
                "INSERT INTO UserRole(RoleId,RoleName) VALUES (@RoleId,@RoleName);SELECT @@identity"
                , new SqlParameter("@RoleId", model.RoleId)
                , new SqlParameter("@RoleName", model.RoleName)
            );
            return Convert.ToInt32(obj);
        }
        #endregion
        #region 删除角色
        /// <summary>
        /// 删除角色
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Delete(int id)
        {
            int rows = SqlHelper.ExecuteNonQuery("DELETE FROM UserRole WHERE RoleId = @id", new SqlParameter("@id", id));
            return rows > 0;
        }
        #endregion
        #region 修改角色
        /// <summary>
        /// 修改角色
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Update(UserRole model)
        {
            string sql = "UPDATE UserRole SET RoleId=@RoleId,RoleName=@RoleName WHERE RoleId=@RoleId";
            int rows = SqlHelper.ExecuteNonQuery(sql
                , new SqlParameter("@RoleId", model.RoleId)
                , new SqlParameter("@RoleName", model.RoleName)
            );
            return rows > 0;
        }
        #endregion
        #region 根据角色Id查询
        /// <summary>
        /// 根据角色Id查询
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public UserRole Get(int id)
        {
            DataTable dt = SqlHelper.ExecuteDataTable("SELECT * FROM UserRole WHERE RoleId=@RoleId", new SqlParameter("@RoleId", id));
            if (dt.Rows.Count > 1)
            {
                throw new Exception("more than 1 row was found");
            }
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            DataRow row = dt.Rows[0];
            UserRole model = ToModel(row);
            return model;
        }
        #endregion
        #region 查询
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        private static UserRole ToModel(DataRow row)
        {
            UserRole model = new UserRole();
            model.RoleId = (int)row["RoleId"];
            model.RoleName = (string)row["RoleName"];
            return model;
        }

        public IEnumerable<UserRole> ListAll()
        {
            List<UserRole> list = new List<UserRole>();
            DataTable dt = SqlHelper.ExecuteDataTable("SELECT * FROM UserRole");
            foreach (DataRow row in dt.Rows)
            {
                list.Add(ToModel(row));
            }
            return list;
        }
        #endregion

        public string GetRoleNameByRoleId(int roleId)
        {
            string sql = "select rolename from userrole where roleid=" + roleId;
            return SqlHelper.ExecuteScalar(sql).ToString();
        }
    }
}
